package com.thinkgsp.webapp.controller;

import java.io.InputStream;
import java.math.BigDecimal;
import java.util.Date;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.thinkgsp.sys.model.ImportCustomer;
import com.thinkgsp.sys.model.ImportProduct;

public class DataImportHandler {
	
	private Workbook workbook;
	
	private Sheet productSheet;
	
	private Sheet customerSheet;
	
	public DataImportHandler(InputStream stream) throws DataImportException {
		try {
			workbook = WorkbookFactory.create(stream);
			productSheet = workbook.getSheet("产品与库存");
			if(productSheet == null) {
				throw new DataImportException("读取工作表[产品与库存]出错。");
			}
			customerSheet = workbook.getSheet("客户与余额");
			if(customerSheet == null) {
				throw new DataImportException("读取工作表[客户与余额]出错。");
			}
		} catch(DataImportException e) {
			throw e;
		} catch(Exception e) {
			throw new DataImportException("读取文件内容出错,原因：" + e.getMessage());
		}
	}
	
	public DataImportResult processImportData() throws DataImportException {
		DataImportResult result = new DataImportResult();
		ProductProcesses pp = new ProductProcesses();
		List<ImportProduct> products = pp.process(productSheet);
		result.setProducts(products);
		CustomerProcesses cp = new CustomerProcesses();
		List<ImportCustomer> customers = cp.process(customerSheet);
		result.setCustomers(customers);
		return result;
	}
	
	public class ProductProcesses {
		public List<ImportProduct> process(Sheet sheet) throws DataImportException {
			List<ImportProduct> products = new ArrayList<ImportProduct>();
			Row header = sheet.getRow(0);
			validateHeader(header);
			for(int i = 1; i <= sheet.getLastRowNum(); i++) {
				ImportProduct product = new ImportProduct();
				try {
					Row row = sheet.getRow(i);
					Cell c1 = row.getCell(0);
					c1.setCellType(Cell.CELL_TYPE_STRING);
					product.setProductCode(c1.getStringCellValue());
					Cell c2 = row.getCell(1);
					String productName = c2.getStringCellValue();
					setProductName(product,productName);
					Cell c3 = row.getCell(2);
					product.setProductSpec(c3.getStringCellValue());
					Cell c4 = row.getCell(3);
					product.setUnit(c4.getStringCellValue());
					Cell c5 = row.getCell(4);
					product.setManuf(c5.getStringCellValue());
					Cell c6 = row.getCell(5);
					if(c6 == null) {
						product.setInnerPack(0);
					} else {
						product.setInnerPack(new Double(c6.getNumericCellValue()).intValue());
					}
					Cell c7 = row.getCell(6);
					product.setBoxPack(new Double(c7.getNumericCellValue()).intValue());
					Cell c8 = row.getCell(7);
					product.setLicenseNo(c8.getStringCellValue());
					Cell c9 = row.getCell(8);
					if("是".equals(c9.getStringCellValue())) {
						product.setBatchMgr(true);
					} else {
						product.setBatchMgr(false);
					}
					Cell c10 = row.getCell(9);
					String barcode = c10.getStringCellValue();
					if(barcode != null && !barcode.isEmpty() && barcode.length() <= 13) {
						product.setBarcode(c10.getStringCellValue());
					}
					
					Cell c11 = row.getCell(10);
					c11.setCellType(Cell.CELL_TYPE_STRING);
					BigDecimal rate = new BigDecimal(c11.getStringCellValue());
					product.setTaxRate(rate);
					Cell c12 = row.getCell(11);
					product.setInvAddress(c12.getStringCellValue());
					Cell c13 = row.getCell(12);
					c13.setCellType(Cell.CELL_TYPE_STRING);
					BigDecimal qty = new BigDecimal(c13.getStringCellValue());
					product.setQuantity(qty);
					Cell c14 = row.getCell(13);
					c14.setCellType(Cell.CELL_TYPE_STRING);
					BigDecimal price = new BigDecimal(c14.getStringCellValue());
					product.setPrice(price);
					Cell c15 = row.getCell(14);
					c15.setCellType(Cell.CELL_TYPE_STRING);
					product.setBatchNo(c15.getStringCellValue());
					Cell c16 = row.getCell(15);
					Date date = c16.getDateCellValue();
					java.sql.Date sd = new java.sql.Date(date.getTime());
					product.setManufDate(sd);
					Cell c17 = row.getCell(16);
					date = c17.getDateCellValue();
					sd = new java.sql.Date(date.getTime());
					product.setExpDate(sd);
				} catch(Exception e) {
					e.printStackTrace();
				}
				products.add(product);
			}
			return products;
		}
		
		private void setProductName(ImportProduct product,String val) {
			if(val == null || val.isEmpty()) return;
			Pattern p = Pattern.compile("(.+)\\((.+)\\)");
			Matcher m = p.matcher(val);
			if(m.matches()) {
				product.setProductName(m.group(1));
				product.setBusName(m.group(2));
			} else {
				product.setProductName(val);
			}
			
		}
		
		private boolean validateHeader(Row header) throws DataImportException {
			if(!"产品编码".equals(header.getCell(0).getStringCellValue())) {
				throw new DataImportException("第一列不是【产品编码】");
			}
			if(!"产品名称".equals(header.getCell(1).getStringCellValue())) {
				throw new DataImportException("第二列不是【产品名称】");
			}
			if(!"产品规格".equals(header.getCell(2).getStringCellValue())) {
				throw new DataImportException("第三列不是【产品规格】");
			}
			if(!"包装单位".equals(header.getCell(3).getStringCellValue())) {
				throw new DataImportException("第四列不是【包装单位】");
			}
			if(!"生产企业".equals(header.getCell(4).getStringCellValue())) {
				throw new DataImportException("第五列不是【生产企业】");
			}
			if(!"中包装数量".equals(header.getCell(5).getStringCellValue())) {
				throw new DataImportException("第六列不是【中包装数量】");
			}
			if(!"箱装数量".equals(header.getCell(6).getStringCellValue())) {
				throw new DataImportException("第七列不是【箱装数量】");
			}
			if(!"批准文号".equals(header.getCell(7).getStringCellValue())) {
				throw new DataImportException("第八列不是【批准文号】");
			}
			if(!"是否批号管理".equals(header.getCell(8).getStringCellValue())) {
				throw new DataImportException("第九列不是【是否批号管理】");
			}
			if(!"产品条码".equals(header.getCell(9).getStringCellValue())) {
				throw new DataImportException("第十列不是【产品条码】");
			}
			if(!"税率".equals(header.getCell(10).getStringCellValue())) {
				throw new DataImportException("第十一列不是【税率】");
			}
			if(!"存货地址".equals(header.getCell(11).getStringCellValue())) {
				throw new DataImportException("第十二列不是【存货地址】");
			}
			if(!"存货数量".equals(header.getCell(12).getStringCellValue())) {
				throw new DataImportException("第十三列不是【存货数量】");
			}
			if(!"单价".equals(header.getCell(13).getStringCellValue())) {
				throw new DataImportException("第十四列不是【单价】");
			}
			if(!"生产批号".equals(header.getCell(14).getStringCellValue())) {
				throw new DataImportException("第十五列不是【生产批号】");
			}
			if(!"生产日期".equals(header.getCell(15).getStringCellValue())) {
				throw new DataImportException("第十六列不是【生产日期】");
			}
			if(!"有效期".equals(header.getCell(16).getStringCellValue())) {
				throw new DataImportException("第十七列不是【有效期】");
			}
			return true;
		}
	}
	
	public class CustomerProcesses {
		public List<ImportCustomer> process(Sheet sheet) throws DataImportException {
			List<ImportCustomer> customers = new ArrayList<ImportCustomer>();
			Row header = sheet.getRow(0);
			validateHeader(header);
			for(int i = 1; i <= sheet.getLastRowNum(); i++) {
				ImportCustomer cust = new ImportCustomer();
				try {
					Row row = sheet.getRow(i);
					Cell c1 = row.getCell(0);
					c1.setCellType(Cell.CELL_TYPE_STRING);
					cust.setCustNo(c1.getStringCellValue());
					Cell c2 = row.getCell(1);
					cust.setCustName(c2.getStringCellValue());
					Cell c3 = row.getCell(2);
					c3.setCellType(Cell.CELL_TYPE_STRING);
					int custType = Integer.parseInt(c3.getStringCellValue());
					cust.setCustType(custType);
					Cell c4 = row.getCell(3);
					cust.setContactMan(c4.getStringCellValue());
					Cell c5 = row.getCell(4);
					c5.setCellType(Cell.CELL_TYPE_STRING);
					cust.setTelephone(c5.getStringCellValue());
					Cell c6 = row.getCell(5);
					c6.setCellType(Cell.CELL_TYPE_STRING);
					cust.setMobilephone(c6.getStringCellValue());
					Cell c7 = row.getCell(6);
					c7.setCellType(Cell.CELL_TYPE_STRING);
					cust.setQq(c7.getStringCellValue());
					Cell c8 = row.getCell(7);
					cust.setEmail(c8.getStringCellValue());
					Cell c9 = row.getCell(8);
					cust.setCompanyAdd(c9.getStringCellValue());
					Cell c10 = row.getCell(9);
					cust.setDeliveryAdd(c10.getStringCellValue());
					Cell c11 = row.getCell(10);
					cust.setDeptName(c11.getStringCellValue());
					Cell c12 = row.getCell(11);
					cust.setEmpName(c12.getStringCellValue());
					Cell c13 = row.getCell(12);
					c13.setCellType(Cell.CELL_TYPE_STRING);
					BigDecimal arecamt = new BigDecimal(c13.getStringCellValue());
					cust.setArecAmount(arecamt);
					Cell c14 = row.getCell(13);
					c14.setCellType(Cell.CELL_TYPE_STRING);
					BigDecimal apayamt = new BigDecimal(c14.getStringCellValue());
					cust.setApayAmount(apayamt);
				} catch(Exception e) {
					e.printStackTrace();
				}
				customers.add(cust);
			}
			return customers;
		}
		
		private boolean validateHeader(Row header) throws DataImportException {
			if(!"客户编码".equals(header.getCell(0).getStringCellValue())) {
				throw new DataImportException("第一列不是【客户编码】");
			}
			if(!"客户名称".equals(header.getCell(1).getStringCellValue())) {
				throw new DataImportException("第二列不是【客户名称】");
			}
			if(!"客户类型".equals(header.getCell(2).getStringCellValue())) {
				throw new DataImportException("第三列不是【客户类型】");
			}
			if(!"联系人".equals(header.getCell(3).getStringCellValue())) {
				throw new DataImportException("第四列不是【联系人】");
			}
			if(!"联系电话".equals(header.getCell(4).getStringCellValue())) {
				throw new DataImportException("第五列不是【联系电话】");
			}
			if(!"手机".equals(header.getCell(5).getStringCellValue())) {
				throw new DataImportException("第六列不是【手机】");
			}
			if(!"QQ".equals(header.getCell(6).getStringCellValue())) {
				throw new DataImportException("第七列不是【QQ】");
			}
			if(!"Email".equals(header.getCell(7).getStringCellValue())) {
				throw new DataImportException("第八列不是【Email】");
			}
			if(!"公司地址".equals(header.getCell(8).getStringCellValue())) {
				throw new DataImportException("第九列不是【公司地址】");
			}
			if(!"送货地址".equals(header.getCell(9).getStringCellValue())) {
				throw new DataImportException("第十列不是【送货地址】");
			}
			if(!"负责部门".equals(header.getCell(10).getStringCellValue())) {
				throw new DataImportException("第十一列不是【负责部门】");
			}
			if(!"负责职员".equals(header.getCell(11).getStringCellValue())) {
				throw new DataImportException("第十二列不是【负责职员】");
			}
			if(!"应收金额".equals(header.getCell(12).getStringCellValue())) {
				throw new DataImportException("第十三列不是【应收金额】");
			}
			if(!"应付金额".equals(header.getCell(13).getStringCellValue())) {
				throw new DataImportException("第十四列不是【应付金额】");
			}
			return true;
		}
	}
}
